package com.iplant.aps.serviceimpl.dao.mss;

import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.mss.MSSBOM;
import com.iplant.aps.service.po.mss.MSSBOMItem;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.MessageFormat;
import java.util.*;
import java.util.stream.Collectors;

public class MSSBOMItemDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(MSSBOMItemDAO.class);

    private static MSSBOMItemDAO Instance = null;

    /**
     * 条件查询集合
     */
    public List<MSSBOMItem> SelectList(BMSEmployee wLoginUser, int wBOMID, int wPartPointID, int wPlaceID,
                                       OutResult<Integer> wErrorCode) {
        List<MSSBOMItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT * FROM {0}.mss_bomitem t1,{0}.mss_material t2 WHERE 1=1 "
                    + "and t1.MaterialID=t2.ID and ( :wBOMID <= 0 or :wBOMID = t1.BOMID ) "
                    + "and ( :wPartPointID <= 0 or :wPartPointID = t1.PartPointID ) "
                    + "and ( :wPlaceID <= 0 or :wPlaceID = t1.PlaceID );", wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wBOMID", wBOMID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wPlaceID", wPlaceID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                MSSBOMItem wItem = new MSSBOMItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.BOMID = StringUtils.parseInt(wReader.get("BOMID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.TypeID = StringUtils.parseInt(wReader.get("TypeID"));
                wItem.MaterialUnit = StringUtils.parseFloat(wReader.get("MaterialUnit"));
                wItem.MaterialUnitRatio = StringUtils.parseFloat(wReader.get("MaterialUnitRatio"));
                wItem.DeviceNo = StringUtils.parseString(wReader.get("DeviceNo"));
                wItem.Author = StringUtils.parseString(wReader.get("Author"));
                wItem.Auditor = StringUtils.parseString(wReader.get("Auditor"));
                wItem.EditTime = StringUtils.parseCalendar(wReader.get("EditTime"));
                wItem.AuditTime = StringUtils.parseCalendar(wReader.get("AuditTime"));
                wItem.LossRatio = StringUtils.parseFloat(wReader.get("LossRatio"));
                wItem.Active = StringUtils.parseInt(wReader.get("Active"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.ParentID = StringUtils.parseInt(wReader.get("ParentID"));
                wItem.GradeID = StringUtils.parseInt(wReader.get("GradeID"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.PlaceID = StringUtils.parseInt(wReader.get("PlaceID"));
                wItem.BOMType = StringUtils.parseInt(wReader.get("BOMType"));
                wItem.MaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));
                wItem.ProductQD = StringUtils.parseString(wReader.get("ProductQD"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceRatio = StringUtils.parseFloat(wReader.get("ReplaceRatio"));
                wItem.OutsourceType = StringUtils.parseInt(wReader.get("OutsourceType"));
                wItem.OriginalType = StringUtils.parseInt(wReader.get("OriginalType"));
                wItem.DisassyType = StringUtils.parseInt(wReader.get("DisassyType"));
                wItem.OrderNum = StringUtils.parseInt(wReader.get("OrderNum"));

                // 需翻译字段
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.UnitText = APSConstans.GetCFGUnitName(wItem.UnitID);

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public List<MSSBOMItem> SelectList(BMSEmployee wLoginUser, List<Integer> wBOMIDList,
                                       OutResult<Integer> wErrorCode) {
        List<MSSBOMItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT * FROM {0}.mss_bomitem t1,{0}.mss_material t2 WHERE 1=1 "
                    + "and t1.MaterialID=t2.ID and   t1.BOMID  in ({1});", wInstance.Result,StringUtils.Join(",", wBOMIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                MSSBOMItem wItem = new MSSBOMItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.BOMID = StringUtils.parseInt(wReader.get("BOMID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.TypeID = StringUtils.parseInt(wReader.get("TypeID"));
                wItem.MaterialUnit = StringUtils.parseFloat(wReader.get("MaterialUnit"));
                wItem.MaterialUnitRatio = StringUtils.parseFloat(wReader.get("MaterialUnitRatio"));
                wItem.DeviceNo = StringUtils.parseString(wReader.get("DeviceNo"));
                wItem.Author = StringUtils.parseString(wReader.get("Author"));
                wItem.Auditor = StringUtils.parseString(wReader.get("Auditor"));
                wItem.EditTime = StringUtils.parseCalendar(wReader.get("EditTime"));
                wItem.AuditTime = StringUtils.parseCalendar(wReader.get("AuditTime"));
                wItem.LossRatio = StringUtils.parseFloat(wReader.get("LossRatio"));
                wItem.Active = StringUtils.parseInt(wReader.get("Active"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.ParentID = StringUtils.parseInt(wReader.get("ParentID"));
                wItem.GradeID = StringUtils.parseInt(wReader.get("GradeID"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.PlaceID = StringUtils.parseInt(wReader.get("PlaceID"));
                wItem.BOMType = StringUtils.parseInt(wReader.get("BOMType"));
                wItem.MaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));
                wItem.ProductQD = StringUtils.parseString(wReader.get("ProductQD"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceRatio = StringUtils.parseFloat(wReader.get("ReplaceRatio"));
                wItem.OutsourceType = StringUtils.parseInt(wReader.get("OutsourceType"));
                wItem.OriginalType = StringUtils.parseInt(wReader.get("OriginalType"));
                wItem.DisassyType = StringUtils.parseInt(wReader.get("DisassyType"));
                wItem.OrderNum = StringUtils.parseInt(wReader.get("OrderNum"));

                // 需翻译字段
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.UnitText = APSConstans.GetCFGUnitName(wItem.UnitID);

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    private MSSBOMItemDAO() {
        super();
    }

    public static MSSBOMItemDAO getInstance() {
        if (Instance == null)
            Instance = new MSSBOMItemDAO();
        return Instance;
    }

    public int QueryMaxBOMID(BMSEmployee wLoginUser, int wOrderID, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            ServiceResult<String> wInstance1 = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance1.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "select max(t1.ID) ID from {0}.mss_bom t1,{1}.oms_order t2 where "
                            + "t2.ID=:OrderID and t1.ProductID=t2.ProductID and t1.LineID=t2.LineID "
                            + "and t1.CustomerID=t2.BureauSectionID and t1.IsStandard=1;",
                    wInstance.Result, wInstance1.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("OrderID", wOrderID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("ID"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public  List<Integer> QueryBOMID(BMSEmployee wLoginUser, int wOrderID, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            ServiceResult<String> wInstance1 = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance1.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "select t1.ID ID from {0}.mss_bom t1 inner join {1}.oms_order  t2 " +
                            " on  t2.RouteID=t1.RouteID and t1.ProductID=t2.ProductID and t1.LineID=t2.LineID" +
                            " and t1.CustomerID=t2.BureauSectionID and t1.IsStandard=1   where "
                            + "t2.ID=:OrderID  ",
                    wInstance.Result, wInstance1.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("OrderID", wOrderID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add( StringUtils.parseInt(wReader.get("ID")));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public Integer QueryCurrentBOMID(BMSEmployee wLoginUser, int wOrderID, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            ServiceResult<String> wInstance1 = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.APS,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance1.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "select t1.ID ID from {0}.mss_bom t1 inner join {1}.oms_order  t2 " +
                            " on  t2.RouteID=t1.RouteID and t1.ProductID=t2.ProductID and t1.LineID=t2.LineID" +
                            " and t1.CustomerID=t2.BureauSectionID and t1.IsStandard=1   where "
                            + "t2.ID=:OrderID  ",
                    wInstance.Result, wInstance1.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("OrderID", wOrderID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult=StringUtils.parseInt(wReader.get("ID"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<Integer> QueryBOMIDList(BMSEmployee wLoginUser, int wLineID,int wProductID,int wCustomerID, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }



            String wSQL = StringUtils.Format(
                    "select  ID from {0}.mss_bom t1 where "
                            + "  t1.ProductID=:wProductID and t1.LineID=:wLineID "
                            + "and t1.CustomerID=:wCustomerID and t1.IsStandard=1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wCustomerID", wCustomerID);

          //  wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add( StringUtils.parseInt(wReader.get("ID")));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }


    /**
     * 条件查询
     */
    public List<MSSBOMItem> SelectList(BMSEmployee wLoginUser, int wBOMID, int wStepID, int wPartID, String wMaterialNo,
                                       String wMaterialName, int wReplaceType, int wOutsourceType,
                                       OutResult<Integer> wErrorCode) {
        List<MSSBOMItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT * FROM {0}.mss_bomitem t1 inner join {0}.mss_material t2 on t1.MaterialID=t2.ID" +
                            "  WHERE   ( :wBOMID <= 0 or :wBOMID = t1.BOMID ) "
                            + "and ( :wPartPointID <= 0 or :wPartPointID = t1.PartPointID ) "
                            + "and ( :wMaterialNo = null or :wMaterialNo = '''' or t2.MaterialNo like ''%{1}%'' ) "
                            + "and ( :wMaterialName = null or :wMaterialName = '''' or t2.MaterialName like ''%{2}%''" +
                            " ) "
                            + "and ( :wReplaceType <= 0 or :wReplaceType = t1.ReplaceType ) "
                            + "and ( :wOutsourceType <= 0 or :wOutsourceType = t1.OutsourceType ) "
                            + "and ( :wPlaceID <= 0 or :wPlaceID = t1.PlaceID );", wInstance.Result, wMaterialNo,
                    wMaterialName);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wBOMID", wBOMID);
            wParamMap.put("wPartPointID", wStepID);
            wParamMap.put("wPlaceID", wPartID);
            wParamMap.put("wMaterialNo", wMaterialNo);
            wParamMap.put("wMaterialName", wMaterialName);
            wParamMap.put("wReplaceType", wReplaceType);
            wParamMap.put("wOutsourceType", wOutsourceType);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                MSSBOMItem wItem = new MSSBOMItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.BOMID = StringUtils.parseInt(wReader.get("BOMID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.TypeID = StringUtils.parseInt(wReader.get("TypeID"));
                wItem.MaterialUnit = StringUtils.parseFloat(wReader.get("MaterialUnit"));
                wItem.MaterialUnitRatio = StringUtils.parseFloat(wReader.get("MaterialUnitRatio"));
                wItem.DeviceNo = StringUtils.parseString(wReader.get("DeviceNo"));
                wItem.Author = StringUtils.parseString(wReader.get("Author"));
                wItem.Auditor = StringUtils.parseString(wReader.get("Auditor"));
                wItem.EditTime = StringUtils.parseCalendar(wReader.get("EditTime"));
                wItem.AuditTime = StringUtils.parseCalendar(wReader.get("AuditTime"));
                wItem.LossRatio = StringUtils.parseFloat(wReader.get("LossRatio"));
                wItem.Active = StringUtils.parseInt(wReader.get("Active"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.ParentID = StringUtils.parseInt(wReader.get("ParentID"));
                wItem.GradeID = StringUtils.parseInt(wReader.get("GradeID"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.PlaceID = StringUtils.parseInt(wReader.get("PlaceID"));
                wItem.BOMType = StringUtils.parseInt(wReader.get("BOMType"));
                wItem.MaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));
                wItem.ProductQD = StringUtils.parseString(wReader.get("ProductQD"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceRatio = StringUtils.parseFloat(wReader.get("ReplaceRatio"));
                wItem.OutsourceType = StringUtils.parseInt(wReader.get("OutsourceType"));
                wItem.OriginalType = StringUtils.parseInt(wReader.get("OriginalType"));
                wItem.DisassyType = StringUtils.parseInt(wReader.get("DisassyType"));
                wItem.OrderNum = StringUtils.parseInt(wReader.get("OrderNum"));

                // 需翻译字段
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.UnitText = APSConstans.GetCFGUnitName(wItem.UnitID);

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 批量激活、禁用标准BOM子项
     */
    public Integer MSS_ActiveListBomItem(BMSEmployee wLoginUser, List<Integer> wIDList, int wActive,
                                         OutResult<Integer> wErrorCode) {
        Integer wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wIDList == null || wIDList.size() <= 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("update {0}.mss_bomitem set Active=:Active where ID in ({1});",
                    wInstance.Result, StringUtils.Join(",", wIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("Active", wActive);

            wSQL = this.DMLChange(wSQL);

            shrisJdbcTemplate.update(wSQL, wParamMap);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public void UpdateOutsourceType(BMSEmployee wLoginUser, int bOMItemID, OutResult<Integer> wErrorCode) {
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return;
            }

            String wSQL = StringUtils.Format("UPDATE {0}.mss_bomitem SET outsourcetype=2 WHERE ID=:ID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", bOMItemID);

            wSQL = this.DMLChange(wSQL);

            shrisJdbcTemplate.update(wSQL, wParamMap);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public List<MSSBOM> SelectMaxBOMList(BMSEmployee wLoginUser, int wProductID, int wLineID,
                                         OutResult<Integer> wErrorCode) {
        List<MSSBOM> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID,CustomerID FROM {0}.mss_bom " +
                            "WHERE ProductID=:ProductID AND LineID=:LineID AND IsStandard=1 ORDER BY CustomerID ASC," +
                            "ID DESC;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ProductID", wProductID);
            wParamMap.put("LineID", wLineID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wID = StringUtils.parseInt(wReader.get("ID"));
                int wCustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                if (wResult.stream().anyMatch(p -> p.CustomerID == wCustomerID)) {
                    continue;
                }

                MSSBOM wMSSBOM = new MSSBOM();
                wMSSBOM.ID = wID;
                wMSSBOM.CustomerID = wCustomerID;
                wResult.add(wMSSBOM);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<MSSBOMItem> SelectEvenBOMItemList(BMSEmployee wLoginUser, List<Integer> wBOMIDList,
                                                  int wPartID, int wPartPointID,
                                                  List<Integer> wMaterialIDList,
                                                  OutResult<Integer> wErrorCode) {
        List<MSSBOMItem> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wBOMIDList == null)
                wBOMIDList = new ArrayList<>();
            if (wMaterialIDList == null)
                wMaterialIDList = new ArrayList<>();

            String wSQL = StringUtils.Format("SELECT * FROM {0}.mss_bomitem t1,{0}.mss_material t2 WHERE 1=1 " +
                            "and t1.MaterialID=t2.ID and t1.bomid IN ({1}) " +
                            "AND t1.ReplaceType=2 AND (:PlaceID<=0 or t1.PlaceID=:PlaceID) " +
                            "AND (:PartPointID<=0 or t1.PartPointID=:PartPointID) AND (:MaterialID=null or " +
                            ":MaterialID='''' or t1.MaterialID IN ({2}));",
                    wInstance.Result, StringUtils.Join(",", wBOMIDList), wMaterialIDList.size() > 0 ?
                            StringUtils.Join(",", wMaterialIDList) : "0");

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("PlaceID", wPartID);
            wParamMap.put("PartPointID", wPartPointID);
            wParamMap.put("MaterialID", StringUtils.Join(",", wMaterialIDList));

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                MSSBOMItem wItem = new MSSBOMItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.BOMID = StringUtils.parseInt(wReader.get("BOMID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.TypeID = StringUtils.parseInt(wReader.get("TypeID"));
                wItem.MaterialUnit = StringUtils.parseFloat(wReader.get("MaterialUnit"));
                wItem.MaterialUnitRatio = StringUtils.parseFloat(wReader.get("MaterialUnitRatio"));
                wItem.DeviceNo = StringUtils.parseString(wReader.get("DeviceNo"));
                wItem.Author = StringUtils.parseString(wReader.get("Author"));
                wItem.Auditor = StringUtils.parseString(wReader.get("Auditor"));
                wItem.EditTime = StringUtils.parseCalendar(wReader.get("EditTime"));
                wItem.AuditTime = StringUtils.parseCalendar(wReader.get("AuditTime"));
                wItem.LossRatio = StringUtils.parseFloat(wReader.get("LossRatio"));
                wItem.Active = StringUtils.parseInt(wReader.get("Active"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.ParentID = StringUtils.parseInt(wReader.get("ParentID"));
                wItem.GradeID = StringUtils.parseInt(wReader.get("GradeID"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.PlaceID = StringUtils.parseInt(wReader.get("PlaceID"));
                wItem.BOMType = StringUtils.parseInt(wReader.get("BOMType"));
                wItem.MaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));
                wItem.ProductQD = StringUtils.parseString(wReader.get("ProductQD"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceRatio = StringUtils.parseFloat(wReader.get("ReplaceRatio"));
                wItem.OutsourceType = StringUtils.parseInt(wReader.get("OutsourceType"));
                wItem.OriginalType = StringUtils.parseInt(wReader.get("OriginalType"));
                wItem.DisassyType = StringUtils.parseInt(wReader.get("DisassyType"));
                wItem.OrderNum = StringUtils.parseInt(wReader.get("OrderNum"));

                // 需翻译字段
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.UnitText = APSConstans.GetCFGUnitName(wItem.UnitID);

                wResult.add(wItem);
            }

            //多条件去重
            wResult = wResult.stream()
                    .collect(Collectors.collectingAndThen(Collectors.toCollection(
                                    () -> new TreeSet<>(Comparator.comparing(o -> o.getPlaceID() + ";"
                                            + o.getPartPointID() + ";" + o.getMaterialID()))),
                            ArrayList::new));
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<MSSBOMItem> SelectOutBOMItemList(BMSEmployee wLoginUser, List<Integer> wBOMIDList, int wPartID,
                                                 int wPartPointID, List<Integer> wMaterialIDList,
                                                 OutResult<Integer> wErrorCode) {
        List<MSSBOMItem> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wBOMIDList == null)
                wBOMIDList = new ArrayList<>();
            if (wMaterialIDList == null)
                wMaterialIDList = new ArrayList<>();

            String wSQL = StringUtils.Format("SELECT * FROM {0}.mss_bomitem t1,{0}.mss_material t2 WHERE 1=1 " +
                            "and t1.MaterialID=t2.ID and t1.bomid IN ({1}) " +
                            "AND t1.OutSourceType in (1,2) AND (:PlaceID<=0 or t1.PlaceID=:PlaceID) " +
                            "AND (:PartPointID<=0 or t1.PartPointID=:PartPointID) AND (:MaterialID=null or " +
                            ":MaterialID='''' or t1.MaterialID IN ({2}));",
                    wInstance.Result, StringUtils.Join(",", wBOMIDList), wMaterialIDList.size() > 0 ?
                            StringUtils.Join(",", wMaterialIDList) : "0");

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("PlaceID", wPartID);
            wParamMap.put("PartPointID", wPartPointID);
            wParamMap.put("MaterialID", StringUtils.Join(",", wMaterialIDList));

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                MSSBOMItem wItem = new MSSBOMItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.BOMID = StringUtils.parseInt(wReader.get("BOMID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.TypeID = StringUtils.parseInt(wReader.get("TypeID"));
                wItem.MaterialUnit = StringUtils.parseFloat(wReader.get("MaterialUnit"));
                wItem.MaterialUnitRatio = StringUtils.parseFloat(wReader.get("MaterialUnitRatio"));
                wItem.DeviceNo = StringUtils.parseString(wReader.get("DeviceNo"));
                wItem.Author = StringUtils.parseString(wReader.get("Author"));
                wItem.Auditor = StringUtils.parseString(wReader.get("Auditor"));
                wItem.EditTime = StringUtils.parseCalendar(wReader.get("EditTime"));
                wItem.AuditTime = StringUtils.parseCalendar(wReader.get("AuditTime"));
                wItem.LossRatio = StringUtils.parseFloat(wReader.get("LossRatio"));
                wItem.Active = StringUtils.parseInt(wReader.get("Active"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.ParentID = StringUtils.parseInt(wReader.get("ParentID"));
                wItem.GradeID = StringUtils.parseInt(wReader.get("GradeID"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.PlaceID = StringUtils.parseInt(wReader.get("PlaceID"));
                wItem.BOMType = StringUtils.parseInt(wReader.get("BOMType"));
                wItem.MaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));
                wItem.ProductQD = StringUtils.parseString(wReader.get("ProductQD"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceRatio = StringUtils.parseFloat(wReader.get("ReplaceRatio"));
                wItem.OutsourceType = StringUtils.parseInt(wReader.get("OutsourceType"));
                wItem.OriginalType = StringUtils.parseInt(wReader.get("OriginalType"));
                wItem.DisassyType = StringUtils.parseInt(wReader.get("DisassyType"));
                wItem.OrderNum = StringUtils.parseInt(wReader.get("OrderNum"));

                // 需翻译字段
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.UnitText = APSConstans.GetCFGUnitName(wItem.UnitID);

                wResult.add(wItem);
            }

            //多条件去重
            wResult = wResult.stream()
                    .collect(Collectors.collectingAndThen(Collectors.toCollection(
                                    () -> new TreeSet<>(Comparator.comparing(o -> o.getPlaceID() + ";"
                                            + o.getPartPointID() + ";" + o.getMaterialID()))),
                            ArrayList::new));
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public boolean JudgeIsHasCurrent(BMSEmployee wLoginUser, int routeID, OutResult<Integer> wErrorCode) {
        boolean wResult = false;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT COUNT(*) FQTY FROM {0}.`mss_bom` WHERE routeid=:routeid AND " +
                            "isStandard=1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("routeid", routeID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                if (StringUtils.parseInt(wReader.get("FQTY")) > 0) {
                    return true;
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
